/*
 Microsoft Public License (Ms-PL)

This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software.

1. Definitions

The terms "reproduce," "reproduction," "derivative works," and "distribution" have the same meaning here as under U.S. copyright law.

A "contribution" is the original software, or any additions or changes to the software.

A "contributor" is any person that distributes its contribution under this license.

"Licensed patents" are a contributor's patent claims that read directly on its contribution.

2. Grant of Rights

(A) Copyright Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free copyright license to reproduce its contribution, prepare derivative works of its contribution, and distribute its contribution or any derivative works that you create.

(B) Patent Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free license under its licensed patents to make, have made, use, sell, offer for sale, import, and/or otherwise dispose of its contribution in the software or derivative works of the contribution in the software.

3. Conditions and Limitations

(A) No Trademark License- This license does not grant you rights to use any contributors' name, logo, or trademarks.

(B) If you bring a patent claim against any contributor over patents that you claim are infringed by the software, your patent license from such contributor to the software ends automatically.

(C) If you distribute any portion of the software, you must retain all copyright, patent, trademark, and attribution notices that are present in the software.

(D) If you distribute any portion of the software in source code form, you may do so only under this license by including a complete copy of this license with your distribution. If you distribute any portion of the software in compiled or object code form, you may only do so under a license that complies with this license.

(E) The software is licensed "as-is." You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. You may have additional consumer rights under your local laws which this license cannot change. To the extent permitted under your local laws, the contributors exclude the implied warranties of merchantability, fitness for a particular purpose and non-infringement.
 */
/*
 *Copyright - Microsoft cooperation  2007
 */
package com.aluna.openxml.excel;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.xml.bind.JAXBContext;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.aluna.openxml.excel.type.ExcelFileType;
import com.aluna.openxml.excel.type.Rels;
import com.aluna.openxml.jaxb.content.Override;
import com.aluna.openxml.jaxb.content.Types;
import com.aluna.openxml.util.XMLUtil;

import de.schlichtherle.io.ArchiveWarningException;
import de.schlichtherle.io.DefaultArchiveDetector;
import de.schlichtherle.io.File;
import de.schlichtherle.io.archive.zip.Zip32Driver;
import de.schlichtherle.util.zip.ZipEntry;
import de.schlichtherle.util.zip.ZipFile;

/**
 * This class represents an XLSX file in the system
 * 
 * @author Liran Zelkha
 *
 */
public class ExcelFile {
	private static final Logger logger = Logger.getLogger(ExcelFile.class.getName());
	private String excelFileName;
	private ZipFile file;
	private String[] sharedStrings;
	private boolean shouldInit = true;

	public ExcelFile(String fileName) {
		try {
			excelFileName = fileName;
			init();
			loadSharedStrings();
		} catch (Exception e) {
			logger.log(Level.SEVERE, "Can't open excel file", e);
			file = null;
		}
	}

	/*
	 * Initialize the file
	 */
	private void init() {
		try {
			if (shouldInit) {
				file = new ZipFile(excelFileName);
				shouldInit = false;
			}
		} catch (Exception e) {
			logger.log(Level.SEVERE, "Can't open excel file", e);
			file = null;
		}

	}

	/**
	 * Close the file
	 */
	public void close() {
		try {
			file.close();
			shouldInit = true;
		} catch (IOException e) {
			logger.log(Level.SEVERE, "Can't close excel file", e);
		}
	}

	/**
	 * 
	 */
	
	public Range getCellsInRange(String sheetName, String sheetId, String startLoc,
			String endLoc) throws Exception {
		return getCellsInRange(sheetName, sheetId, startLoc, endLoc, null,-1);
	}

	/**
	 * Get a range of cells in a specific sheet.
	 * TODO: Currently we ignore the start col and end col (rows are treated) 
	 * @param sheetName The name of the sheet (as it appears in the XLSX file)
	 * @param sheetId The ID of the sheet (name)
	 * @param startLoc The start location of the range in the sheet
	 * @param endLoc The end location of the range in the sheet
	 * @param symbol a value where only cells with this value will be inserted into the range
	 * @param index the index of the cell in the range that should have the value
	 * @return
	 * @throws Exception
	 */
	public Range getCellsInRange(String sheetName, String sheetId, String startLoc,
			String endLoc, String symbol, int index) throws Exception {

		int startRow = rowFromString(startLoc);
		int endRow = rowFromString(endLoc);
		
		ZipEntry zipEntryFile = file.getEntry("xl/worksheets/" + sheetName
				+ ".xml");

		Document doc = XMLUtil.parse(file.getInputStream(zipEntryFile));

		Node sheetData = doc.getElementsByTagName("sheetData").item(0);
		NodeList rows = sheetData.getChildNodes();

		Range cellMap = new Range(sheetId, startLoc, endLoc);

		for (int i = 0; i < rows.getLength(); ++i) {
			Node row = rows.item(i);
			NodeList cells = row.getChildNodes();
			int rowIndex = Integer.parseInt(row.getAttributes().getNamedItem(
					"r").getNodeValue());

			if ((rowIndex >= startRow && rowIndex <= endRow) || (startRow == -1 || endRow == -1)) {
				boolean addThisLine = true;
				for (int cellIndex = 0; cellIndex < cells.getLength() && addThisLine; ++cellIndex) {
					
					Node cell = cells.item(cellIndex);
	
					Object data = null;
					if (cell.getAttributes().getNamedItem("t") != null
							&& cell.getAttributes().getNamedItem("t")
									.getNodeValue().equalsIgnoreCase("s")) {
						int textIndex = Integer.parseInt(cell.getChildNodes().item(
								0).getTextContent());
						data = sharedStrings[textIndex];
					} else {
						if (cell.getChildNodes().item(0) != null) {
							Float f = Float.parseFloat(cell.getChildNodes().item(0)
									.getTextContent());
							data = f;
						}
					}
	
					Cell c = new Cell();
					c.setData(data);
					String location = cell.getAttributes().getNamedItem("r")
							.getNodeValue();
					c.setLocation(location);
					
					if (index != -1 && cellIndex == index && !data.toString().equals(symbol)) {
						addThisLine = false;
					} else {
						cellMap.put(rowIndex, c);
					}
				}
			}
		}

		return cellMap;
	}
	
	private int rowFromString(String startLoc) {
		if (startLoc == null)
			return -1;
		
		String[] locs = startLoc.split("\\$");
		
		return Integer.parseInt(locs[2]);
	}
	
	/*
	 * Load the shared strings section of an Excel XLSX file
	 */
	private void loadSharedStrings() throws Exception {
		ZipEntry sharedStringsFile = file.getEntry("xl/sharedStrings.xml");

		Document sharedStringsDoc = XMLUtil.parse(file
				.getInputStream(sharedStringsFile));
		NodeList sharedStringsList = sharedStringsDoc.getElementsByTagName("t");

		sharedStrings = new String[sharedStringsList.getLength()];

		for (int i = 0; i < sharedStringsList.getLength(); ++i) {
			sharedStrings[i] = sharedStringsList.item(i).getTextContent();
		}
	}

	/**
	 * Add an ExcelFileType to the current Excel file. References are not updated.
	 * @param obj
	 * @param fileName
	 * @throws Exception
	 */
	public void add(ExcelFileType obj, String fileName) throws Exception {
		DefaultArchiveDetector detector = new DefaultArchiveDetector("xlsx2",new Zip32Driver());

		close();

		File f = new File(excelFileName, detector);
		File son = new File(f, fileName, detector);
		
		son.createNewFile();
		son.copyFrom(new ByteArrayInputStream(obj.store()));
		File.umount();

		if (!(obj instanceof Rels))
			addContent(obj, fileName);
	}

	/**
	 * Update an existing worksheet in the current Excel file. 
	 * @param sheet
	 */
	public void updateWorksheet(ExcelFileType sheet) {
		try {
			DefaultArchiveDetector detector = new DefaultArchiveDetector("xlsx2",new Zip32Driver());

			close();
		
			File f = new File(excelFileName, detector);
			File son = new File(f, sheet.getFileName(), detector);

			ByteArrayInputStream bais = new ByteArrayInputStream(sheet.update(this));
			
			close();
			
			son.copyFrom(bais);
			
			File.update();
		} catch (ArchiveWarningException awe) {
			// Nothing to do - just a warning
		} catch (Exception e) {
			logger.log(Level.SEVERE, "Can't update worksheet", e);
		}
	}

	/*
	 * Add content of a file to an Excel XLSX flie
	 */
	private void addContent(ExcelFileType obj, String fileName) {
		try {
			init();

			DefaultArchiveDetector detector = new DefaultArchiveDetector("xlsx2",new Zip32Driver());

			JAXBContext context = JAXBContext.newInstance("com.aluna.openxml.jaxb.content");
			
			Unmarshaller unmarshaller = context.createUnmarshaller();
			
			Types t = (Types)unmarshaller.unmarshal(getInputStream("[Content_Types].xml"));
			com.aluna.openxml.jaxb.content.Override override = new Override();
			override.setContentType(obj.getContentType());
			override.setPartName("/" + fileName);
			t.getContent().add(override);

			close();
			
			File f = new File(excelFileName, detector);
			File son = new File(f, "[Content_Types].xml", detector);

			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			
			Marshaller marshaller = context.createMarshaller();
			
			marshaller.marshal(t, baos);
			
			son.copyFrom(new ByteArrayInputStream(baos.toByteArray()));
			File.update();

		} catch (Exception e) {
			logger.log(Level.SEVERE, "Can't add contents to excel file", e);
		}
	}
	
	/**
	 * Get the input stream of the file
	 * @param name
	 * @return
	 * @throws Exception
	 */
	public InputStream getInputStream(String name) throws Exception {
		init();
		return file.getInputStream(name);
	}

	/**
	 * Flush any updates that occured in the Excel XLSX file
	 *
	 */
	public void flush() {
		try {
			File.update();
			file.close();
		} catch (Exception e) {
			logger.log(Level.SEVERE, "Can't flush excel file", e);
		}
	}
}
